package com.ms.server.init;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import com.message.ui.sys.utils.AdminUserUtil;
import com.ms.env.EnvUtil;
import com.system.comm.utils.FrameSpringBeanUtil;
import com.system.dao.BaseDao;
import com.system.dao.utils.DbUtil;
import com.system.ds.DbContextHolder;

/**
 * 脚本升级
 * @author yuejing
 * @date 2018年12月18日 上午11:17:25
 */
public class DbUpdate {

	private static final Logger LOGGER = LoggerFactory.getLogger(DbUpdate.class);

	/**
	 * 初始化更新后的脚本
	 */
	public static void init() {
		if (EnvUtil.projectDbUpdate() != 1) {
			return;
		}
		ThreadPoolTaskExecutor task = FrameSpringBeanUtil.getBean(ThreadPoolTaskExecutor.class);
		task.execute(new Runnable() {

			@Override
			public void run() {
				updateSql();
			}

		});
	}
	private static void updateSql() {
		DbContextHolder.setDsName("dataSource1");
		BaseDao baseDao = new BaseDao();
		if (DbUtil.isMysql()) {
			version1_0_0(baseDao);
			version1_0_1(baseDao);
		} else if (DbUtil.isOracle()) {
			// TODO 带扩展Oracle的初始化脚本
		}

	}
	private static void version1_0_0(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from admin_user limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[admin_user]: " + e.getMessage());
			String sql = "create table admin_user\r\n" + 
					"(\r\n" + 
					"   id                   varchar(32) not null comment '编号',\r\n" + 
					"   username             varchar(30) not null comment '用户名',\r\n" + 
					"   password             varchar(80) not null comment '密码',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table admin_user comment '管理员表'";
			baseDao.updateSql(sql);
			sql = "insert into `admin_user` (`id`, `username`, `password`, `create_time`)\r\n" + 
					"values('1','admin','" + AdminUserUtil.encodePassword("123456") + "','2017-12-27 15:40:59')";
			baseDao.saveSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from msg_group limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[msg_group]: " + e.getMessage());
			String sql = "create table msg_group\r\n" + 
					"(\r\n" + 
					"   id                   varchar(32) not null comment '编号',\r\n" + 
					"   sys_no               varchar(50) not null comment '来源系统',\r\n" + 
					"   type                 int not null comment '类型[10系统、20个人]',\r\n" + 
					"   name                 varchar(50) not null comment '名称',\r\n" + 
					"   pid                  varchar(32) not null comment '父编号',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   primary key (id, sys_no)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table msg_group comment '消息分组表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from msg_info limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[msg_info]: " + e.getMessage());
			String sql = "create table msg_info\r\n" + 
					"(\r\n" + 
					"   id                   varchar(32) not null comment '编号',\r\n" + 
					"   group_id             varchar(32) not null comment '消息分组编号',\r\n" + 
					"   title                varchar(255) not null comment '标题',\r\n" + 
					"   content              text comment '内容',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   send_user_id         varchar(32) not null comment '发送人',\r\n" + 
					"   status               int not null comment '状态[10待发送、20已发送]',\r\n" + 
					"   send_time            datetime not null comment '发送时间',\r\n" + 
					"   type                 int not null comment '类型[10阅读、20待办]',\r\n" + 
					"   ext1                 varchar(255) comment '扩展1',\r\n" + 
					"   ext2                 varchar(255) comment '扩展2',\r\n" + 
					"   ext3                 varchar(255) comment '扩展3',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table msg_info comment '消息表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from msg_rece limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[msg_rece]: " + e.getMessage());
			String sql = "create table msg_rece\r\n" + 
					"(\r\n" + 
					"   id                   varchar(32) not null comment '编号',\r\n" + 
					"   msg_id               varchar(32) not null comment '消息编号',\r\n" + 
					"   rece_sys_no          varchar(50) not null comment '接收人来源系统',\r\n" + 
					"   rece_user_id         varchar(32) not null comment '接收人',\r\n" + 
					"   rece_time            datetime not null comment '接收时间',\r\n" + 
					"   is_read              int not null comment '是否阅读',\r\n" + 
					"   read_time            datetime comment '阅读时间',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table msg_rece comment '消息接收表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from send_email limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[send_email]: " + e.getMessage());
			String sql = "create table send_email\r\n" + 
					"(\r\n" + 
					"   id                   varchar(32) not null comment '编号',\r\n" + 
					"   msg_id               varchar(32) not null comment '消息编号',\r\n" + 
					"   email                varchar(100) not null comment '接收邮箱',\r\n" + 
					"   title                varchar(200) not null comment '邮件标题',\r\n" + 
					"   content              text not null comment '发送内容',\r\n" + 
					"   send_time            datetime not null comment '发送时间',\r\n" + 
					"   status               int not null comment '状态[10待发送、20发送中、30发送成功、40发送失败]',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   serv_no              varchar(32) not null comment '处理服务的唯一编码',\r\n" + 
					"   files                text comment '附件集合[多个;分隔]',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table send_email comment '发送邮件信息表'";
			baseDao.updateSql(sql);
			sql = "create unique index uq_msg_id_email on send_email\r\n" + 
					"(\r\n" + 
					"   msg_id,\r\n" + 
					"   email\r\n" + 
					")";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from send_sms limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[send_sms]: " + e.getMessage());
			String sql = "create table send_sms\r\n" + 
					"(\r\n" + 
					"   id                   varchar(32) not null comment '编号',\r\n" + 
					"   msg_id               varchar(32) not null comment '消息编号',\r\n" + 
					"   phone                varchar(30) not null comment '手机号',\r\n" + 
					"   content              text not null comment '发送内容',\r\n" + 
					"   send_time            datetime not null comment '发送时间',\r\n" + 
					"   status               int not null comment '状态[10待发送、20发送中、30发送成功、40发送失败]',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   serv_no              varchar(32) not null comment '处理服务的唯一编码',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table send_sms comment '发送短信信息表'";
			baseDao.updateSql(sql);
			sql = "create unique index uq_msg_id_phone on send_sms\r\n" + 
					"(\r\n" + 
					"   msg_id,\r\n" + 
					"   phone\r\n" + 
					")";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from sys_info limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_info]: " + e.getMessage());
			String sql = "create table sys_info\r\n" + 
					"(\r\n" + 
					"   sys_no               varchar(50) not null comment '系统编码',\r\n" + 
					"   name                 varchar(150) not null comment '系统名称',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   client_id          	 varchar(32) null comment '客户端编号',\r\n" + 
					"   token          		 varchar(150) null comment '密钥',\r\n" + 
					"   primary key (sys_no)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table sys_info comment '来源系统表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from user_group_rule limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[user_group_rule]: " + e.getMessage());
			String sql = "create table user_group_rule\r\n" + 
					"(\r\n" + 
					"   id                   varchar(32) not null comment '编号',\r\n" + 
					"   group_id             varchar(32) not null comment '分组编号',\r\n" + 
					"   sys_no               varchar(32) not null comment '来源系统',\r\n" + 
					"   user_id              varchar(32) not null comment '用户编码',\r\n" + 
					"   status               int not null comment '状态[10打开、20关闭]',\r\n" + 
					"   email_status         int not null comment '发邮件[10打开、20关闭]',\r\n" + 
					"   sms_status           int not null comment '发短信[10打开、20关闭]',\r\n" + 
					"   rece_email           varchar(200) comment '接收邮箱[多个用;分隔]',\r\n" + 
					"   rece_phone           varchar(200) comment '接收手机',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table user_group_rule comment '用户消息分组规则表'";
			baseDao.updateSql(sql);
			sql = "create unique index uq_groupId_sysNo_userId on user_group_rule\r\n" + 
					"(\r\n" + 
					"   group_id,\r\n" + 
					"   sys_no,\r\n" + 
					"   user_id\r\n" + 
					")";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}

		try {
			baseDao.queryForLong("select count(*) from user_info limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[user_info]: " + e.getMessage());
			String sql = "create table user_info\r\n" + 
					"(\r\n" + 
					"   id                   varchar(32) not null comment '编号',\r\n" + 
					"   sys_no               varchar(50) not null comment '来源系统',\r\n" + 
					"   user_id              varchar(32) not null comment '用户编号',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   phone                varchar(255) comment '手机号[多个;分隔]',\r\n" + 
					"   email                varchar(255) comment '邮箱[多个;分隔]',\r\n" + 
					"   primary key (id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table user_info comment '用户表'";
			baseDao.updateSql(sql);
			sql = "create unique index uq_userid_sourcesys on user_info\r\n" + 
					"(\r\n" + 
					"   sys_no,\r\n" + 
					"   user_id\r\n" + 
					")";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}
	}
	private static void version1_0_1(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from mot_event limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[mot_event]: " + e.getMessage());
			String sql = "create table mot_event\r\n" + 
					"(\r\n" + 
					"   me_id                varchar(32) not null comment '编号',\r\n" + 
					"   sys_no               varchar(50) not null comment '来源系统编码',\r\n" + 
					"   name                 varchar(150) not null comment '名称',\r\n" + 
					"   remark               varchar(256) comment '备注',\r\n" + 
					"   req_encrypt          int not null comment '调度加密方式[10无、20md5(clientId+时间戳+token)]',\r\n" + 
					"   filter_plug          varchar(128) not null comment '过滤插件',\r\n" + 
					"   create_user_id       varchar(32) not null comment '创建人',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   primary key (me_id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table mot_event comment '事件表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}
		
		try {
			baseDao.queryForLong("select count(*) from mot_sub limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[mot_sub]: " + e.getMessage());
			String sql = "create table mot_sub\r\n" + 
					"(\r\n" + 
					"   ms_id                varchar(32) not null comment '编号',\r\n" + 
					"   sys_no               varchar(32) not null comment '来源系统编号',\r\n" + 
					"   me_id                varchar(32) not null comment '事件编号',\r\n" + 
					"   sub_user_id          varchar(32) not null comment '订阅人',\r\n" + 
					"   rece_email           text comment '邮件通知号码[多个;分隔]',\r\n" + 
					"   rece_sms             text comment '短信通知号码[多个;分隔]',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   primary key (ms_id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table mot_sub comment '事件订阅人'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}
		
		try {
			baseDao.queryForLong("select count(*) from mot_sub_filter limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[mot_sub_filter]: " + e.getMessage());
			String sql = "create table mot_sub_filter\r\n" + 
					"(\r\n" + 
					"   msf_id               varchar(32) not null comment '编号',\r\n" + 
					"   ms_id                varchar(32) not null comment '事件订阅人编号',\r\n" + 
					"   type                 varchar(128) not null comment '类型',\r\n" + 
					"   field1               varchar(512) not null comment '字段1',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   field2               varchar(512) comment '字段2',\r\n" + 
					"   field3               varchar(512) comment '字段3',\r\n" + 
					"   field4               varchar(512) comment '字段4',\r\n" + 
					"   field5               int comment '字段5',\r\n" + 
					"   field6               float comment '字段6',\r\n" + 
					"   field7               text comment '字段7',\r\n" + 
					"   field8               text comment '字段8',\r\n" + 
					"   primary key (msf_id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table mot_sub_filter comment '事件订阅人过滤表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}
		
		try {
			baseDao.queryForLong("select count(*) from mot_push limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[mot_push]: " + e.getMessage());
			String sql = "create table mot_push\r\n" + 
					"(\r\n" + 
					"   mp_id                varchar(32) not null comment '编号',\r\n" + 
					"   sys_no               varchar(50) not null comment '来源系统',\r\n" + 
					"   me_id                varchar(32) not null comment '事件编号',\r\n" + 
					"   title                varchar(512) not null comment '标题',\r\n" + 
					"   content              text comment '内容',\r\n" + 
					"   rece_user_ids        text not null comment '接收人[多个;分隔，0代表所有订阅人]',\r\n" + 
					"   email_content        text comment '邮件内容',\r\n" + 
					"   email_files          text comment '邮件附件[多个;分隔]',\r\n" + 
					"   sms_content          varchar(512) comment '短信内容',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   primary key (mp_id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table mot_push comment '推送事件表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}
		
		try {
			baseDao.queryForLong("select count(*) from mot_push_filter limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[mot_push_filter]: " + e.getMessage());
			String sql = "create table mot_push_filter\r\n" + 
					"(\r\n" + 
					"   mpf_id               varchar(32) not null comment '编号',\r\n" + 
					"   mp_id                varchar(32) not null comment '推送事件编号',\r\n" + 
					"   type                 varchar(128) not null comment '类型',\r\n" + 
					"   field1               varchar(512) not null comment '字段1',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   field2               varchar(512) comment '字段2',\r\n" + 
					"   field3               varchar(512) comment '字段3',\r\n" + 
					"   field4               varchar(512) comment '字段4',\r\n" + 
					"   field5               int comment '字段5',\r\n" + 
					"   field6               float comment '字段6',\r\n" + 
					"   field7               text comment '字段7',\r\n" + 
					"   field8               text comment '字段8',\r\n" + 
					"   primary key (mpf_id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table mot_push_filter comment '推送事件过滤表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}
		
		try {
			baseDao.queryForLong("select count(*) from mot_push_rece limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[mot_push_rece]: " + e.getMessage());
			String sql = "create table mot_push_rece\r\n" + 
					"(\r\n" + 
					"   mp_id                varchar(32) not null comment '推送事件编号',\r\n" + 
					"   ms_id                varchar(32) not null comment '事件订阅人编号',\r\n" + 
					"   msg_id               varchar(32) not null comment '消息编号',\r\n" + 
					"   create_time          datetime not null comment '创建时间',\r\n" + 
					"   primary key (mp_id, ms_id, msg_id)\r\n" + 
					")";
			baseDao.updateSql(sql);
			sql = "alter table mot_push_rece comment '事件推送实际接收表'";
			baseDao.updateSql(sql);
			LOGGER.info("|===================================================");
		}
	}
}
